﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Windows.Forms;

namespace FilmBlowingMachine.Winform.Lib
{
    class ExcelHelper
    {
        /// <summary>
        /// 导入EXCEL
        /// </summary>
        /// <returns>数据表格结果</returns>
        public DataSet ImportExcel()
        {
            DataSet ds = new DataSet();
            DataTable dt;
            OpenFileDialog sflg = new OpenFileDialog();
            sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            if (sflg.ShowDialog() == DialogResult.Cancel)
            {
                return null;
            }
            FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read);
            IWorkbook book;
            if (sflg.FilterIndex == 1)
            {
                book = new HSSFWorkbook(fs);
            }
            else
            {
                book = new XSSFWorkbook(fs);
            }
            int exceptionTableIndex = 0;
            int exceptionRowIndex = 0;
            int exceptionColumnIndex = 0;
            int sheetCount = book.NumberOfSheets;
            try
            {
                for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
                {
                    exceptionColumnIndex = sheetIndex;
                    ISheet sheet = book.GetSheetAt(sheetIndex);
                    if (sheet == null) continue;

                    IRow row = sheet.GetRow(0);
                    if (row == null) continue;

                    int firstCellNum = row.FirstCellNum;//从0开始的第一个有数据的列
                    int lastCellNum = row.LastCellNum;//FirstCellNum+列数
                    if (firstCellNum == lastCellNum) continue;

                    dt = new DataTable(sheet.SheetName);
                    for (int i = firstCellNum; i < lastCellNum; i++)
                    {
                        dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string));//强制设为string类型
                    }

                    for (int i = 1; i <= sheet.LastRowNum; i++)
                    {
                        exceptionRowIndex = i;
                        DataRow newRow = dt.Rows.Add();
                        for (int j = firstCellNum; j < lastCellNum; j++)
                        {
                            exceptionColumnIndex = j;
                            ICell cell = sheet.GetRow(i).GetCell(j);
                            if (cell != null)
                            {
                                CellType type = cell.CellType;
                                if (type == CellType.Formula)//公式实际的value可能是数字，可能是字符串,根据Cell的CachedFormulaResultType判断
                                {
                                    if (cell.CachedFormulaResultType == CellType.Numeric)
                                    {
                                        newRow[j] = cell.NumericCellValue.ToString();
                                    }
                                    if (cell.CachedFormulaResultType == CellType.String)
                                    {
                                        newRow[j] = cell.StringCellValue;
                                    }
                                }
                                else
                                {
                                    newRow[j] = sheet.GetRow(i).GetCell(j).ToString();
                                }
                            }
                            else
                            {
                                newRow[j] = "";
                            }
                        }
                    }
                    ds.Tables.Add(dt);
                }
            }
            catch (Exception theException)
            {
                throw new Exception(string.Format("导入EXCEL文件失败！{3}表格{0}行{1}列{2}有错误！", exceptionTableIndex, exceptionRowIndex, exceptionColumnIndex, theException.Message));
            }

            return ds;
        }

        /// <summary>
        /// DataTable导出EXCEL（使用自定义列名）
        /// </summary>
        /// <param name="dataTable">数据表格数据源</param>
        /// <param name="colNames">数据源列名集合</param>
        public void ExportExcel(DataTable dataTable, string[] colNames)
        {
            SaveFileDialog sflg = new SaveFileDialog();
            sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            if (sflg.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }
            IWorkbook book;
            if (sflg.FilterIndex == 1)
            {
                book = new HSSFWorkbook();
            }
            else
            {
                book = new XSSFWorkbook();
            }
            ISheet sheet = book.CreateSheet("sheet1");
            // 添加表头
            IRow row = sheet.CreateRow(0);
            for (int n = 0; n < dataTable.Columns.Count; n++)
            {
                ICell cell = row.CreateCell(n);
                cell.SetCellType(CellType.String);
                cell.SetCellValue(colNames[n]);
            }
            // 添加数据
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {

                    ICell cell = row.CreateCell(j);
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(dataTable.Rows[i][j].ToString());
                }
            }
            // 写入 
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
            ms.Close();
            ms.Dispose();
            Process.Start(sflg.FileName);
        }

        /// <summary>
        /// DataTable导出EXCEL（使用数据表中列名）
        /// </summary>
        /// <param name="dataTable">数据表格数据源</param>
        public void ExportExcel(DataTable dataTable)
        {
            SaveFileDialog sflg = new SaveFileDialog();
            sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            if (sflg.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }
            IWorkbook workbook;
            if (sflg.FilterIndex == 1)
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                workbook = new XSSFWorkbook();
            }
            ISheet sheet = workbook.CreateSheet("sheet1");
            // 添加表头
            IRow row = sheet.CreateRow(0);
            for (int n = 0; n < dataTable.Columns.Count; n++)
            {
                ICell cell = row.CreateCell(n);
                cell.SetCellType(CellType.String);
                cell.SetCellValue(dataTable.Columns[n].ColumnName);
            }
            // 添加数据
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {

                    ICell cell = row.CreateCell(j);
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(dataTable.Rows[i][j].ToString());
                }
            }
            // 写入 

            using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write))
            {

                workbook.Write(fs);
            }


        }

        /// <summary>
        /// DataTable导出EXCEL
        /// </summary>
        /// <param name="dataTable">数据表格数据源</param>
        /// <param name="filePath">文件存储路径（含文件名）</param>
        public void ExportExcel(DataTable dataTable, string filePath)
        {
            if (filePath == null) return;

            IWorkbook workbook = new HSSFWorkbook();

            ISheet sheet = workbook.CreateSheet("sheet1");
            // 添加表头
            IRow row = sheet.CreateRow(0);
            for (int n = 0; n < dataTable.Columns.Count; n++)
            {
                ICell cell = row.CreateCell(n);
                cell.SetCellType(CellType.String);
                cell.SetCellValue(dataTable.Columns[n].ColumnName);
            }
            // 添加数据
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {

                    ICell cell = row.CreateCell(j);
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(dataTable.Rows[i][j].ToString());
                }
            }
            // 写入 
            //MemoryStream ms = new MemoryStream();
            //book.Write(ms);
            using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                //byte[] data = ms.ToArray();
                //fs.Write(data, 0, data.Length);
                //fs.Flush();
                workbook.Write(fs);
            }
            //ms.Close();
            //ms.Dispose();
            //Process.Start(sflg.FileName);

        }

        /// <summary>
        /// DataGridView导出EXCEL
        /// </summary>
        /// <param name="dataGridView">数据源</param>
        public void ExportExcel(DataGridView dataGridView)
        {
            SaveFileDialog sflg = new SaveFileDialog();
            sflg.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            if (sflg.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }
            IWorkbook workbook;//创建一个新的Excel文件
            if (sflg.FilterIndex == 1)
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                workbook = new XSSFWorkbook();
            }

            FileStream fs = null;//创建一个新的文件流
            ISheet sheet = null;//为Excel创建一张工作表

            //定义行数、列数、与当前Excel已有行数
            int rowCount = dataGridView.RowCount;//记录表格中的行数
            int colCount = dataGridView.ColumnCount;//记录表格中的列数

            //为了防止出错，这里应该判定一下文件与文件是否存在

            //创建工作表
            try
            {
                fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write);
                sheet = workbook.CreateSheet("Sheet1");
                IRow row = sheet.CreateRow(0);
                for (int j = 0; j < colCount; j++)  //列循环
                {
                    if (dataGridView.Columns[j].Visible && dataGridView.Rows[0].Cells[j].Value != null)
                    {
                        ICell cell = row.CreateCell(j);//创建列
                        cell.SetCellValue(dataGridView.Columns[j].HeaderText.ToString());//更改单元格值                  
                    }
                }
            }
            catch
            {
                MessageBox.Show("创建工作表失败！");
                return;
            }

            try
            {
                for (int i = 0; i < rowCount; i++)      //行循环
                {
                    //防止行数超过Excel限制
                    if (i >= 65536)
                    {
                        MessageBox.Show("行数超过Excel限制！");
                        return;
                    }
                    IRow row = sheet.CreateRow(1 + i);  //创建行
                    for (int j = 0; j < colCount; j++)  //列循环
                    {
                        if (dataGridView.Columns[j].Visible && dataGridView.Rows[i].Cells[j].Value != null)
                        {
                            ICell cell = row.CreateCell(j);//创建列
                            cell.SetCellValue(dataGridView.Rows[i].Cells[j].Value.ToString());//更改单元格值                  
                        }
                    }
                }

                workbook.Write(fs);
                MessageBox.Show("导出成功！");

            }
            catch
            {
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                    fs.Dispose();

                }
                workbook = null;
            }
        }

        /// <summary>
        /// 选择文件存储路径（指定默认文件名）
        /// </summary>
        /// <param name="fileName">默认文件名</param>
        /// <returns></returns>
        private string SelectSaveFilePath(string fileName)
        {
            string saveFileName = "";

            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xlsx";
            saveDialog.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return ""; //被点了取消

            return saveFileName;
        }

        /// <summary>
        /// 选择文件存储路径
        /// </summary>
        /// <returns></returns>
        private string SelectSaveFilePath()
        {
            return SelectSaveFilePath("");
        }
    }
}
